package com.matt.repository.impl;

import com.matt.foundation.vo.CountProductVo;
import com.matt.foundation.vo.CountTransferVo;
import com.matt.repository.CountProductRepository;
import com.matt.repository.base.BaseNativeSqlRepository;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.util.List;

@Slf4j
@Service
public class CountProductNativeRepositoryImpl extends BaseNativeSqlRepository implements CountProductRepository {

	@Override
	public List<CountProductVo> countMostProduct(String areaId) {

		String querySql = "SELECT p.name as productName, c.productId as productId,	count(c.id) AS countNum"
				+ " FROM "
				+ "	`contract_product` c LEFT JOIN product p ON p.id = c.productId "
				+ " LEFT JOIN contract cc ON cc.id = c.contractId "
				+ " WHERE	c.deleted = 0 AND p.deleted = 0";
				querySql+= String.format(" AND  cc.areaId = '%s' ",areaId);
				querySql+= " GROUP BY	c.productId "
				+ "ORDER BY	countNum DESC "
				+ "LIMIT 0,5";
		
		return  sqlObjectList(querySql,CountProductVo.class);
	}

	@Override
	public List<CountProductVo> countSaleProduct(String areaId) {
		
		
		String querySql = "SELECT	p.`name` as productName,	c.productId as productId,count(c.id) AS countNum, SUM(c.count-c.remainCount) AS saleCount "
				+ "FROM	"
				+ "contract_product c LEFT JOIN product p ON p.id = c.productId "
				+ " LEFT JOIN contract cc ON cc.id = c.contractId "
				+ "WHERE	c.productType = 2 AND	p.deleted = 0 ";
				querySql+= String.format(" AND  cc.areaId = '%s' ",areaId);
				querySql+= " GROUP BY p.id ORDER BY	saleCount DESC "
				+ "LIMIT 0,5";
		return sqlObjectList(querySql,CountProductVo.class);
	}

	@Override
	public List<CountProductVo> countTotal(String areaId) {

		String querySql = "SELECT	count(c.id) AS countNum "
				+ "FROM	"
				+ "contract_product c LEFT JOIN product p ON p.id = c.productId "
				+ " LEFT JOIN contract cc ON cc.id = c.contractId "
				+ "WHERE	c.deleted = 0 AND	p.deleted = 0 ";
		querySql+= String.format(" AND  cc.areaId = '%s'",areaId);

		return sqlObjectList(querySql,CountProductVo.class);
	}

	@Override
	public List<CountProductVo> countSaleTotal(String areaId) {
		
		String querySql = "SELECT " + 				
				"	COUNT(c.id) AS countNum " + 
				" FROM " + 
				"	contract_product c " + 
				" LEFT JOIN product p ON p.id = c.productId " + 
				" LEFT JOIN contract cc ON cc.id = c.contractId " +
				" WHERE " + 
				"	c.productType = 2 " + 
				" AND " + 
				"	p.deleted = 0 ";
		querySql+= String.format(" AND  cc.areaId = '%s'",areaId);
		
		return sqlObjectList(querySql,CountProductVo.class);
	}

	@Override
	public List<CountTransferVo> countLeadsTransferClient(String staffId,String areaId) {
		String querySql = "SELECT lh.leadsId FROM leads_history lh WHERE	"
				+ "lh.deleted = 0 AND lh.clienteleStatus = 3 AND lh.type = 'clientele' AND DATE_FORMAT( lh.created, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) ";
				querySql +=String.format( " AND staffId = '%s' ",staffId);
				querySql +=String.format( " AND areaId = '%s' ",areaId);
				querySql+= " GROUP BY lh.leadsId";
		
		
		return sqlObjectList(querySql,CountTransferVo.class);
	}

	@Override
	public List<CountTransferVo> countContractByStaffId(String staffId,StringBuilder leadsId,String areaId) {
		
		String querySql = "SELECT " + 
				"	sl.id AS leadsId " +
				"FROM " + 
				"	stat_leads sl " + 
				"WHERE " + 
				"	DATE_FORMAT( sl.created, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) " ;
				querySql +=String.format(" AND sl.staffId = '%s' ",staffId);
				querySql +=String.format(" AND sl.leadsId in ( %s) ",leadsId);
				querySql +=String.format( " AND sl.areaId = '%s' ",areaId);
				querySql +="AND sl.`type` = 3 " + 
				"AND sl.category = 1";
		return sqlObjectList(querySql,CountTransferVo.class);
	}

	@Override
	public List<CountTransferVo> countLeadscCurrentMonth(String staffId,String areaId) {

		String querySql ="SELECT COUNT(id) AS countTotal FROM leads "
				+ "WHERE deleted = 0 AND DATE_FORMAT( created, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )";
				querySql +=String.format( " AND staffId = '%s'",staffId);
				querySql +=String.format( " AND areaId = '%s' ",areaId);
		
		return sqlObjectList(querySql,CountTransferVo.class);
	}

	@Override
	public List<CountTransferVo> countContractPrice(String staffId,String areaId) {
		
		String querySql ="SELECT  " + 
				"	SUM(totalAmount) AS countPrice, " + 
				"	COUNT(id) AS countTotal " + 
				"FROM " + 
				"	contract " + 
				" WHERE " + 				
				"	deleted = 0 " + 
				"AND " + 
				"	DATE_FORMAT( created, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )";
		querySql +=String.format( " AND	staffId = '%s' " ,staffId);
		querySql +=String.format( " AND areaId = '%s' ",areaId);
		
		return sqlObjectList(querySql,CountTransferVo.class);
	}

	@Override
	public List<CountTransferVo> countRenewalContract(String staffId,String areaId) {

		String querySql ="SELECT  " + 
				"	SUM(totalAmount) AS countPrice, " + 
				"	COUNT(id) AS countTotal " + 
				"FROM " + 
				"	contract " + 
				" WHERE " + 				
				"	deleted = 0 " + 
				" AND	isRenewal = 1 " + 
				"AND " + 
				"	DATE_FORMAT( created, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )";
		querySql +=String.format( " AND	staffId = '%s' " ,staffId);
		querySql +=String.format( " AND areaId = '%s' ",areaId);
		
		return sqlObjectList(querySql,CountTransferVo.class);
		
	}

	@Override
	public List<CountTransferVo> countAverageTime(String staffId) {

		String querySql = "SELECT " + 
				"	c.id AS leadsId, " + 
				"  l.created AS leadsCreated, " + 
				"	c.created AS clientCreated, " + 
				"	ct.created AS contractCreated " + 
				"FROM  " + 
				"	clientele c  " + 
				"LEFT JOIN leads l ON l.id = c.leadsId " + 
				"LEFT JOIN contract ct ON ct.clienteleId = c.id " + 
				"WHERE " + 
				"	c.deleted = 0 " + 
				"AND l.deleted = 0 " + 
				"AND ct.deleted = 0 " ; 
				querySql +=String.format("AND c.staffId = '%s' "  ,staffId);
				querySql +=String.format("AND l.staffId = '%s' "  ,staffId);
				querySql +=String.format("AND ct.staffId = '%s' " ,staffId); 
				querySql +="GROUP BY ct.clienteleId";
		
		return sqlObjectList(querySql,CountTransferVo.class);
	}

	@Override
	public List<CountTransferVo> countStayRenewalContract(String staffId) {
		String querySql = "SELECT  " + 
				"	con.id AS id " + 
				" FROM " + 
				"	contract_product AS cp " + 
				" INNER JOIN product AS pr ON cp.productId = pr.id " + 
				" INNER JOIN clientele AS cli ON cli.id = cp.clienteleId " + 
				" INNER JOIN contract AS con ON con.id = cp.contractId " + 
				" WHERE " + 
				"	pr.type = 1 " + 
				" AND TO_DAYS(con.endDate) - TO_DAYS(NOW()) < 30 " + 
				" AND TO_DAYS(con.endDate) - TO_DAYS(NOW()) > 0 " + 
				" AND con. STATUS = 1 " ; 
				querySql +=String.format(" AND cli.staffId = '%s' " ,staffId);
				querySql +=String.format(" AND con.staffId = '%s'  " ,staffId);
				querySql +=" AND DATE_FORMAT( con.endDate, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) " + 
				" union " + 
				" SELECT  " + 
				"	con.id AS id " + 
				" FROM " + 
				"	contract_product AS cp " + 
				" INNER JOIN product AS pr ON cp.productId = pr.id " + 
				" INNER JOIN clientele AS cli ON cli.id = cp.clienteleId " + 
				" INNER JOIN contract AS con ON cp.contractId = con.id " + 
				" WHERE " + 
				"	pr.type = 2 " + 
				" AND con. STATUS = 1 " + 
				" AND cp.remainCount < 8 " + 
				" AND cp.remainCount > 0 "; 
				querySql +=String.format(" AND cli.staffId = '%s' ",staffId); 
				querySql +=String.format(" AND con.staffId = '%s'  ",staffId);
				querySql +=" AND DATE_FORMAT( con.endDate, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )";
		return sqlObjectList(querySql,CountTransferVo.class);
	}
}
